{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('2018-02-06', 'Utrecht', 'Red', 42.0, 15.14)\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect(\":memory:\") as con:\n",
    "    c = con.cursor()\n",
    "    c.execute('''CREATE TABLE sensors(date TEXT, \n",
    "                city TEXT, code TEXT, sensor_id REAL, temperature REAL);''')\n",
    "    c.execute(\"INSERT INTO sensors VALUES ('2018-02-06', 'Utrecht', 'Red', 42, 15.14)\")\n",
    "    c.execute(\"SELECT * FROM sensors\")\n",
    "    print(c.fetchone())\n",
    "    con.execute(\"DROP TABLE sensors\")\n",
    "    print(c.execute(\"SELECT COUNT(*) FROM sqlite_master WHERE type='table'\").fetchone()[0])\n",
    "    c.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID =  1\n",
      "NAME =  Paul\n",
      "ADDRESS =  California\n",
      "SALARY =  20000.0 \n",
      "\n",
      "ID =  2\n",
      "NAME =  Allen\n",
      "ADDRESS =  Texas\n",
      "SALARY =  15000.0 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "con = sqlite3.connect(\":memory:\")\n",
    "c = con.cursor()\n",
    "c.execute('''CREATE TABLE COMPANY\n",
    "       (ID INT PRIMARY KEY     NOT NULL,\n",
    "       NAME           TEXT    NOT NULL,\n",
    "       AGE            INT     NOT NULL,\n",
    "       ADDRESS        CHAR(50),\n",
    "       SALARY         REAL)''')\n",
    "c.execute(\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \\\n",
    "      VALUES (1, 'Paul', 32, 'California', 20000.00 )\")\n",
    "c.execute(\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \\\n",
    "      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )\")\n",
    "cursor = c.execute(\"SELECT id, name, address, salary  from COMPANY\")\n",
    "for row in cursor:\n",
    "   print(\"ID = \", row[0])\n",
    "   print(\"NAME = \", row[1])\n",
    "   print(\"ADDRESS = \", row[2])\n",
    "   print(\"SALARY = \", row[3], \"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing database from Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "from pandas.io.sql import read_sql\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1700.0, 5.0), (1701.0, 11.0), (1702.0, 16.0), (1703.0, 23.0), (1704.0, 36.0), (1705.0, 58.0), (1706.0, 29.0), (1707.0, 20.0), (1708.0, 10.0), (1709.0, 8.0), (1710.0, 3.0), (1711.0, 0.0), (1712.0, 0.0), (1713.0, 2.0), (1714.0, 11.0), (1715.0, 27.0), (1716.0, 47.0), (1717.0, 63.0), (1718.0, 60.0), (1719.0, 39.0), (1720.0, 28.0), (1721.0, 26.0), (1722.0, 22.0), (1723.0, 11.0), (1724.0, 21.0), (1725.0, 40.0), (1726.0, 78.0), (1727.0, 122.0), (1728.0, 103.0), (1729.0, 73.0), (1730.0, 47.0), (1731.0, 35.0), (1732.0, 11.0), (1733.0, 5.0), (1734.0, 16.0), (1735.0, 34.0), (1736.0, 70.0), (1737.0, 81.0), (1738.0, 111.0), (1739.0, 101.0), (1740.0, 73.0), (1741.0, 40.0), (1742.0, 20.0), (1743.0, 16.0), (1744.0, 5.0), (1745.0, 11.0), (1746.0, 22.0), (1747.0, 40.0), (1748.0, 60.0), (1749.0, 80.9), (1750.0, 83.4), (1751.0, 47.7), (1752.0, 47.8), (1753.0, 30.7), (1754.0, 12.2), (1755.0, 9.6), (1756.0, 10.2), (1757.0, 32.4), (1758.0, 47.6), (1759.0, 54.0), (1760.0, 62.9), (1761.0, 85.9), (1762.0, 61.2), (1763.0, 45.1), (1764.0, 36.4), (1765.0, 20.9), (1766.0, 11.4), (1767.0, 37.8), (1768.0, 69.8), (1769.0, 106.1), (1770.0, 100.8), (1771.0, 81.6), (1772.0, 66.5), (1773.0, 34.8), (1774.0, 30.6), (1775.0, 7.0), (1776.0, 19.8), (1777.0, 92.5), (1778.0, 154.4), (1779.0, 125.9), (1780.0, 84.8), (1781.0, 68.1), (1782.0, 38.5), (1783.0, 22.8), (1784.0, 10.2), (1785.0, 24.1), (1786.0, 82.9), (1787.0, 132.0), (1788.0, 130.9), (1789.0, 118.1), (1790.0, 89.9), (1791.0, 66.6), (1792.0, 60.0), (1793.0, 46.9), (1794.0, 41.0), (1795.0, 21.3), (1796.0, 16.0), (1797.0, 6.4), (1798.0, 4.1), (1799.0, 6.8), (1800.0, 14.5), (1801.0, 34.0), (1802.0, 45.0), (1803.0, 43.1), (1804.0, 47.5), (1805.0, 42.2), (1806.0, 28.1), (1807.0, 10.1), (1808.0, 8.1), (1809.0, 2.5), (1810.0, 0.0), (1811.0, 1.4), (1812.0, 5.0), (1813.0, 12.2), (1814.0, 13.9), (1815.0, 35.4), (1816.0, 45.8), (1817.0, 41.1), (1818.0, 30.1), (1819.0, 23.9), (1820.0, 15.6), (1821.0, 6.6), (1822.0, 4.0), (1823.0, 1.8), (1824.0, 8.5), (1825.0, 16.6), (1826.0, 36.3), (1827.0, 49.6), (1828.0, 64.2), (1829.0, 67.0), (1830.0, 70.9), (1831.0, 47.8), (1832.0, 27.5), (1833.0, 8.5), (1834.0, 13.2), (1835.0, 56.9), (1836.0, 121.5), (1837.0, 138.3), (1838.0, 103.2), (1839.0, 85.7), (1840.0, 64.6), (1841.0, 36.7), (1842.0, 24.2), (1843.0, 10.7), (1844.0, 15.0), (1845.0, 40.1), (1846.0, 61.5), (1847.0, 98.5), (1848.0, 124.7), (1849.0, 96.3), (1850.0, 66.6), (1851.0, 64.5), (1852.0, 54.1), (1853.0, 39.0), (1854.0, 20.6), (1855.0, 6.7), (1856.0, 4.3), (1857.0, 22.7), (1858.0, 54.8), (1859.0, 93.8), (1860.0, 95.8), (1861.0, 77.2), (1862.0, 59.1), (1863.0, 44.0), (1864.0, 47.0), (1865.0, 30.5), (1866.0, 16.3), (1867.0, 7.3), (1868.0, 37.6), (1869.0, 74.0), (1870.0, 139.0), (1871.0, 111.2), (1872.0, 101.6), (1873.0, 66.2), (1874.0, 44.7), (1875.0, 17.0), (1876.0, 11.3), (1877.0, 12.4), (1878.0, 3.4), (1879.0, 6.0), (1880.0, 32.3), (1881.0, 54.3), (1882.0, 59.7), (1883.0, 63.7), (1884.0, 63.5), (1885.0, 52.2), (1886.0, 25.4), (1887.0, 13.1), (1888.0, 6.8), (1889.0, 6.3), (1890.0, 7.1), (1891.0, 35.6), (1892.0, 73.0), (1893.0, 85.1), (1894.0, 78.0), (1895.0, 64.0), (1896.0, 41.8), (1897.0, 26.2), (1898.0, 26.7), (1899.0, 12.1), (1900.0, 9.5), (1901.0, 2.7), (1902.0, 5.0), (1903.0, 24.4), (1904.0, 42.0), (1905.0, 63.5), (1906.0, 53.8), (1907.0, 62.0), (1908.0, 48.5), (1909.0, 43.9), (1910.0, 18.6), (1911.0, 5.7), (1912.0, 3.6), (1913.0, 1.4), (1914.0, 9.6), (1915.0, 47.4), (1916.0, 57.1), (1917.0, 103.9), (1918.0, 80.6), (1919.0, 63.6), (1920.0, 37.6), (1921.0, 26.1), (1922.0, 14.2), (1923.0, 5.8), (1924.0, 16.7), (1925.0, 44.3), (1926.0, 63.9), (1927.0, 69.0), (1928.0, 77.8), (1929.0, 64.9), (1930.0, 35.7), (1931.0, 21.2), (1932.0, 11.1), (1933.0, 5.7), (1934.0, 8.7), (1935.0, 36.1), (1936.0, 79.7), (1937.0, 114.4), (1938.0, 109.6), (1939.0, 88.8), (1940.0, 67.8), (1941.0, 47.5), (1942.0, 30.6), (1943.0, 16.3), (1944.0, 9.6), (1945.0, 33.2), (1946.0, 92.6), (1947.0, 151.6), (1948.0, 136.3), (1949.0, 134.7), (1950.0, 83.9), (1951.0, 69.4), (1952.0, 31.5), (1953.0, 13.9), (1954.0, 4.4), (1955.0, 38.0), (1956.0, 141.7), (1957.0, 190.2), (1958.0, 184.8), (1959.0, 159.0), (1960.0, 112.3), (1961.0, 53.9), (1962.0, 37.6), (1963.0, 27.9), (1964.0, 10.2), (1965.0, 15.1), (1966.0, 47.0), (1967.0, 93.8), (1968.0, 105.9), (1969.0, 105.5), (1970.0, 104.5), (1971.0, 66.6), (1972.0, 68.9), (1973.0, 38.0), (1974.0, 34.5), (1975.0, 15.5), (1976.0, 12.6), (1977.0, 27.5), (1978.0, 92.5), (1979.0, 155.4), (1980.0, 154.6), (1981.0, 140.4), (1982.0, 115.9), (1983.0, 66.6), (1984.0, 45.9), (1985.0, 17.9), (1986.0, 13.4), (1987.0, 29.4), (1988.0, 100.2), (1989.0, 157.6), (1990.0, 142.6), (1991.0, 145.7), (1992.0, 94.3), (1993.0, 54.6), (1994.0, 29.9), (1995.0, 17.5), (1996.0, 8.6), (1997.0, 21.5), (1998.0, 64.3), (1999.0, 93.3), (2000.0, 119.6), (2001.0, 111.0), (2002.0, 104.0), (2003.0, 63.7), (2004.0, 40.4), (2005.0, 29.8), (2006.0, 15.2), (2007.0, 7.5), (2008.0, 2.9)]\n",
      "(309,)\n",
      "Deleted 217 rows\n"
     ]
    }
   ],
   "source": [
    "with sqlite3.connect(\":memory:\") as con:\n",
    "    c = con.cursor()\n",
    "    data_loader = sm.datasets.sunspots.load_pandas()\n",
    "    df = data_loader.data\n",
    "    rows = [tuple(x) for x in df.values]\n",
    "    print(rows)\n",
    "    \n",
    "    con.execute(\"CREATE TABLE sunspots(year, sunactivity)\")\n",
    "    con.executemany(\"INSERT INTO sunspots(year, sunactivity) VALUES (?,?)\", rows)\n",
    "    c.execute(\"SELECT COUNT(*) FROM sunspots\")\n",
    "    print(c.fetchone())\n",
    "    print(\"Deleted\", con.execute(\"DELETE FROM sunspots where sunactivity > 20\").rowcount, \"rows\")\n",
    "    \n",
    "    c.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, ForeignKey, Integer, String, Float\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import relationship\n",
    "from sqlalchemy import create_engine, UniqueConstraint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('sensor', MetaData(bind=None), Column('id', Integer(), table=<sensor>, primary_key=True, nullable=False), Column('observation', Integer(), table=<sensor>), Column('multiplier', Float(), table=<sensor>), Column('station_id', Integer(), ForeignKey('station.id'), table=<sensor>), schema=None)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Base = declarative_base()\n",
    "\n",
    "class Station(Base):\n",
    "    __tablename__ = 'station'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String(14), nullable=False, unique=True)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"Id=%d name=%s\" % (self.id, self.name)\n",
    "    \n",
    "class Sensor(Base):\n",
    "    __tablename__ = 'sensor'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    observation = Column(Integer)\n",
    "    multiplier = Column(Float)\n",
    "    station_id = Column(Integer, ForeignKey('station.id'))\n",
    "    station = relationship(Station)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"Id=%d oberservation=%d multiplier=%.1f station_id=%d\" % \\\n",
    "                (self.id, self.observation, self.multiplier, self.station_id)\n",
    "\n",
    "Sensor.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "import os\n",
    "from pandas.io.sql import read_sql\n",
    "\n",
    "def populate(session):\n",
    "    de_bilt = Station(name='De Bilt')\n",
    "    session.add(de_bilt)\n",
    "    session.add(Station(name='Utrecht'))\n",
    "    session.commit()\n",
    "    print(\"Station\", de_bilt)\n",
    "    \n",
    "    temp_sensor = Sensor(observation=20, multiplier=.1, station=de_bilt)\n",
    "    session.add(temp_sensor)\n",
    "    session.commit()\n",
    "    print(\"Sensor\", temp_sensor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('station', MetaData(bind=None), Column('id', Integer(), table=<station>, primary_key=True, nullable=False), Column('name', String(length=14), table=<station>, nullable=False), schema=None)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Station.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-04-21 00:01:21,702 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2018-04-21 00:01:21,706 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,708 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2018-04-21 00:01:21,711 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,714 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(\"station\")\n",
      "2018-04-21 00:01:21,716 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,720 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(\"sensor\")\n",
      "2018-04-21 00:01:21,723 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,727 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE station (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR(14) NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tUNIQUE (name)\n",
      ")\n",
      "\n",
      "\n",
      "2018-04-21 00:01:21,731 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,737 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2018-04-21 00:01:21,742 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE sensor (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tobservation INTEGER, \n",
      "\tmultiplier FLOAT, \n",
      "\tstation_id INTEGER, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(station_id) REFERENCES station (id)\n",
      ")\n",
      "\n",
      "\n",
      "2018-04-21 00:01:21,745 INFO sqlalchemy.engine.base.Engine ()\n",
      "2018-04-21 00:01:21,750 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2018-04-21 00:01:21,756 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2018-04-21 00:01:21,759 INFO sqlalchemy.engine.base.Engine INSERT INTO station (name) VALUES (?)\n",
      "2018-04-21 00:01:21,762 INFO sqlalchemy.engine.base.Engine ('test',)\n",
      "2018-04-21 00:01:21,767 INFO sqlalchemy.engine.base.Engine SELECT station.id AS station_id, station.name AS station_name \n",
      "FROM station \n",
      "WHERE station.name = ?\n",
      " LIMIT ? OFFSET ?\n",
      "2018-04-21 00:01:21,769 INFO sqlalchemy.engine.base.Engine ('test', 1, 0)\n",
      "Id=1 name=test\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine(\"sqlite:///test.db\", echo=True)\n",
    "Base.metadata.create_all(engine)\n",
    "#Base.metadata.bind = engine\n",
    "DBSession = sessionmaker(bind=engine)\n",
    "session = DBSession()\n",
    "station = Station(name='test')\n",
    "session.add(station)\n",
    "test_query = session.query(Station).filter_by(name='test').first() \n",
    "print(test_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-04-21 00:01:36,693 INFO sqlalchemy.engine.base.Engine INSERT INTO station (name) VALUES (?)\n",
      "2018-04-21 00:01:36,696 INFO sqlalchemy.engine.base.Engine ('De Bilt',)\n",
      "2018-04-21 00:01:36,699 INFO sqlalchemy.engine.base.Engine INSERT INTO station (name) VALUES (?)\n",
      "2018-04-21 00:01:36,702 INFO sqlalchemy.engine.base.Engine ('Utrecht',)\n",
      "2018-04-21 00:01:36,707 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "Station 2018-04-21 00:01:36,713 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2018-04-21 00:01:36,715 INFO sqlalchemy.engine.base.Engine SELECT station.id AS station_id, station.name AS station_name \n",
      "FROM station \n",
      "WHERE station.id = ?\n",
      "2018-04-21 00:01:36,717 INFO sqlalchemy.engine.base.Engine (2,)\n",
      "Id=2 name=De Bilt\n",
      "2018-04-21 00:01:36,725 INFO sqlalchemy.engine.base.Engine INSERT INTO sensor (observation, multiplier, station_id) VALUES (?, ?, ?)\n",
      "2018-04-21 00:01:36,728 INFO sqlalchemy.engine.base.Engine (20, 0.1, 2)\n",
      "2018-04-21 00:01:36,732 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "Sensor 2018-04-21 00:01:36,740 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2018-04-21 00:01:36,743 INFO sqlalchemy.engine.base.Engine SELECT sensor.id AS sensor_id, sensor.observation AS sensor_observation, sensor.multiplier AS sensor_multiplier, sensor.station_id AS sensor_station_id \n",
      "FROM sensor \n",
      "WHERE sensor.id = ?\n",
      "2018-04-21 00:01:36,746 INFO sqlalchemy.engine.base.Engine (1,)\n",
      "Id=1 oberservation=20 multiplier=0.1 station_id=2\n"
     ]
    }
   ],
   "source": [
    "populate(session)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_query is station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-04-21 00:08:01,283 INFO sqlalchemy.engine.base.Engine SELECT station.id AS station_id, station.name AS station_name \n",
      "FROM station \n",
      "WHERE station.name = ?\n",
      " LIMIT ? OFFSET ?\n",
      "2018-04-21 00:08:01,286 INFO sqlalchemy.engine.base.Engine ('De Bilt', 1, 0)\n",
      "2018-04-21 00:08:01,291 INFO sqlalchemy.engine.base.Engine SELECT station.id AS station_id, station.name AS station_name \n",
      "FROM station\n",
      "2018-04-21 00:08:01,296 INFO sqlalchemy.engine.base.Engine ()\n",
      "[Id=1 name=test, Id=2 name=De Bilt, Id=3 name=Utrecht]\n",
      "2018-04-21 00:08:01,301 INFO sqlalchemy.engine.base.Engine SELECT sensor.id AS sensor_id, sensor.observation AS sensor_observation, sensor.multiplier AS sensor_multiplier, sensor.station_id AS sensor_station_id \n",
      "FROM sensor\n",
      "2018-04-21 00:08:01,303 INFO sqlalchemy.engine.base.Engine ()\n",
      "[Id=1 oberservation=20 multiplier=0.1 station_id=2]\n",
      "2018-04-21 00:08:01,309 INFO sqlalchemy.engine.base.Engine SELECT sensor.id AS sensor_id, sensor.observation AS sensor_observation, sensor.multiplier AS sensor_multiplier, sensor.station_id AS sensor_station_id \n",
      "FROM sensor \n",
      "WHERE ? = sensor.station_id\n",
      "2018-04-21 00:08:01,312 INFO sqlalchemy.engine.base.Engine (2,)\n",
      "Id=1 oberservation=20 multiplier=0.1 station_id=2\n",
      "   id     name\n",
      "0   1     test\n",
      "1   2  De Bilt\n",
      "2   3  Utrecht\n",
      "DB deleted\n"
     ]
    }
   ],
   "source": [
    "station_query = session.query(Station).filter_by(name='De Bilt').first()\n",
    "print(session.query(Station).all())\n",
    "print(session.query(Sensor).all())\n",
    "print(session.query(Sensor).filter(Sensor.station==station_query).one())\n",
    "print(read_sql(\"SELECT * FROM station\", engine.raw_connection()))\n",
    "\n",
    "try:\n",
    "    os.remove('test.db')\n",
    "    print('DB deleted')\n",
    "except OSError:\n",
    "    print('DB delete failed')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MongoDB and PyMongo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       YEAR  SUNACTIVITY\n",
      "0    1700.0          5.0\n",
      "1    1701.0         11.0\n",
      "2    1702.0         16.0\n",
      "3    1703.0         23.0\n",
      "4    1704.0         36.0\n",
      "5    1705.0         58.0\n",
      "6    1706.0         29.0\n",
      "7    1707.0         20.0\n",
      "8    1708.0         10.0\n",
      "9    1709.0          8.0\n",
      "10   1710.0          3.0\n",
      "11   1711.0          0.0\n",
      "12   1712.0          0.0\n",
      "13   1713.0          2.0\n",
      "14   1714.0         11.0\n",
      "15   1715.0         27.0\n",
      "16   1716.0         47.0\n",
      "17   1717.0         63.0\n",
      "18   1718.0         60.0\n",
      "19   1719.0         39.0\n",
      "20   1720.0         28.0\n",
      "21   1721.0         26.0\n",
      "22   1722.0         22.0\n",
      "23   1723.0         11.0\n",
      "24   1724.0         21.0\n",
      "25   1725.0         40.0\n",
      "26   1726.0         78.0\n",
      "27   1727.0        122.0\n",
      "28   1728.0        103.0\n",
      "29   1729.0         73.0\n",
      "..      ...          ...\n",
      "279  1979.0        155.4\n",
      "280  1980.0        154.6\n",
      "281  1981.0        140.4\n",
      "282  1982.0        115.9\n",
      "283  1983.0         66.6\n",
      "284  1984.0         45.9\n",
      "285  1985.0         17.9\n",
      "286  1986.0         13.4\n",
      "287  1987.0         29.4\n",
      "288  1988.0        100.2\n",
      "289  1989.0        157.6\n",
      "290  1990.0        142.6\n",
      "291  1991.0        145.7\n",
      "292  1992.0         94.3\n",
      "293  1993.0         54.6\n",
      "294  1994.0         29.9\n",
      "295  1995.0         17.5\n",
      "296  1996.0          8.6\n",
      "297  1997.0         21.5\n",
      "298  1998.0         64.3\n",
      "299  1999.0         93.3\n",
      "300  2000.0        119.6\n",
      "301  2001.0        111.0\n",
      "302  2002.0        104.0\n",
      "303  2003.0         63.7\n",
      "304  2004.0         40.4\n",
      "305  2005.0         29.8\n",
      "306  2006.0         15.2\n",
      "307  2007.0          7.5\n",
      "308  2008.0          2.9\n",
      "\n",
      "[309 rows x 2 columns]\n",
      "dict_values([{'YEAR': 1700.0, 'SUNACTIVITY': 5.0}, {'YEAR': 1701.0, 'SUNACTIVITY': 11.0}, {'YEAR': 1702.0, 'SUNACTIVITY': 16.0}, {'YEAR': 1703.0, 'SUNACTIVITY': 23.0}, {'YEAR': 1704.0, 'SUNACTIVITY': 36.0}, {'YEAR': 1705.0, 'SUNACTIVITY': 58.0}, {'YEAR': 1706.0, 'SUNACTIVITY': 29.0}, {'YEAR': 1707.0, 'SUNACTIVITY': 20.0}, {'YEAR': 1708.0, 'SUNACTIVITY': 10.0}, {'YEAR': 1709.0, 'SUNACTIVITY': 8.0}, {'YEAR': 1710.0, 'SUNACTIVITY': 3.0}, {'YEAR': 1711.0, 'SUNACTIVITY': 0.0}, {'YEAR': 1712.0, 'SUNACTIVITY': 0.0}, {'YEAR': 1713.0, 'SUNACTIVITY': 2.0}, {'YEAR': 1714.0, 'SUNACTIVITY': 11.0}, {'YEAR': 1715.0, 'SUNACTIVITY': 27.0}, {'YEAR': 1716.0, 'SUNACTIVITY': 47.0}, {'YEAR': 1717.0, 'SUNACTIVITY': 63.0}, {'YEAR': 1718.0, 'SUNACTIVITY': 60.0}, {'YEAR': 1719.0, 'SUNACTIVITY': 39.0}, {'YEAR': 1720.0, 'SUNACTIVITY': 28.0}, {'YEAR': 1721.0, 'SUNACTIVITY': 26.0}, {'YEAR': 1722.0, 'SUNACTIVITY': 22.0}, {'YEAR': 1723.0, 'SUNACTIVITY': 11.0}, {'YEAR': 1724.0, 'SUNACTIVITY': 21.0}, {'YEAR': 1725.0, 'SUNACTIVITY': 40.0}, {'YEAR': 1726.0, 'SUNACTIVITY': 78.0}, {'YEAR': 1727.0, 'SUNACTIVITY': 122.0}, {'YEAR': 1728.0, 'SUNACTIVITY': 103.0}, {'YEAR': 1729.0, 'SUNACTIVITY': 73.0}, {'YEAR': 1730.0, 'SUNACTIVITY': 47.0}, {'YEAR': 1731.0, 'SUNACTIVITY': 35.0}, {'YEAR': 1732.0, 'SUNACTIVITY': 11.0}, {'YEAR': 1733.0, 'SUNACTIVITY': 5.0}, {'YEAR': 1734.0, 'SUNACTIVITY': 16.0}, {'YEAR': 1735.0, 'SUNACTIVITY': 34.0}, {'YEAR': 1736.0, 'SUNACTIVITY': 70.0}, {'YEAR': 1737.0, 'SUNACTIVITY': 81.0}, {'YEAR': 1738.0, 'SUNACTIVITY': 111.0}, {'YEAR': 1739.0, 'SUNACTIVITY': 101.0}, {'YEAR': 1740.0, 'SUNACTIVITY': 73.0}, {'YEAR': 1741.0, 'SUNACTIVITY': 40.0}, {'YEAR': 1742.0, 'SUNACTIVITY': 20.0}, {'YEAR': 1743.0, 'SUNACTIVITY': 16.0}, {'YEAR': 1744.0, 'SUNACTIVITY': 5.0}, {'YEAR': 1745.0, 'SUNACTIVITY': 11.0}, {'YEAR': 1746.0, 'SUNACTIVITY': 22.0}, {'YEAR': 1747.0, 'SUNACTIVITY': 40.0}, {'YEAR': 1748.0, 'SUNACTIVITY': 60.0}, {'YEAR': 1749.0, 'SUNACTIVITY': 80.9}, {'YEAR': 1750.0, 'SUNACTIVITY': 83.4}, {'YEAR': 1751.0, 'SUNACTIVITY': 47.7}, {'YEAR': 1752.0, 'SUNACTIVITY': 47.8}, {'YEAR': 1753.0, 'SUNACTIVITY': 30.7}, {'YEAR': 1754.0, 'SUNACTIVITY': 12.2}, {'YEAR': 1755.0, 'SUNACTIVITY': 9.6}, {'YEAR': 1756.0, 'SUNACTIVITY': 10.2}, {'YEAR': 1757.0, 'SUNACTIVITY': 32.4}, {'YEAR': 1758.0, 'SUNACTIVITY': 47.6}, {'YEAR': 1759.0, 'SUNACTIVITY': 54.0}, {'YEAR': 1760.0, 'SUNACTIVITY': 62.9}, {'YEAR': 1761.0, 'SUNACTIVITY': 85.9}, {'YEAR': 1762.0, 'SUNACTIVITY': 61.2}, {'YEAR': 1763.0, 'SUNACTIVITY': 45.1}, {'YEAR': 1764.0, 'SUNACTIVITY': 36.4}, {'YEAR': 1765.0, 'SUNACTIVITY': 20.9}, {'YEAR': 1766.0, 'SUNACTIVITY': 11.4}, {'YEAR': 1767.0, 'SUNACTIVITY': 37.8}, {'YEAR': 1768.0, 'SUNACTIVITY': 69.8}, {'YEAR': 1769.0, 'SUNACTIVITY': 106.1}, {'YEAR': 1770.0, 'SUNACTIVITY': 100.8}, {'YEAR': 1771.0, 'SUNACTIVITY': 81.6}, {'YEAR': 1772.0, 'SUNACTIVITY': 66.5}, {'YEAR': 1773.0, 'SUNACTIVITY': 34.8}, {'YEAR': 1774.0, 'SUNACTIVITY': 30.6}, {'YEAR': 1775.0, 'SUNACTIVITY': 7.0}, {'YEAR': 1776.0, 'SUNACTIVITY': 19.8}, {'YEAR': 1777.0, 'SUNACTIVITY': 92.5}, {'YEAR': 1778.0, 'SUNACTIVITY': 154.4}, {'YEAR': 1779.0, 'SUNACTIVITY': 125.9}, {'YEAR': 1780.0, 'SUNACTIVITY': 84.8}, {'YEAR': 1781.0, 'SUNACTIVITY': 68.1}, {'YEAR': 1782.0, 'SUNACTIVITY': 38.5}, {'YEAR': 1783.0, 'SUNACTIVITY': 22.8}, {'YEAR': 1784.0, 'SUNACTIVITY': 10.2}, {'YEAR': 1785.0, 'SUNACTIVITY': 24.1}, {'YEAR': 1786.0, 'SUNACTIVITY': 82.9}, {'YEAR': 1787.0, 'SUNACTIVITY': 132.0}, {'YEAR': 1788.0, 'SUNACTIVITY': 130.9}, {'YEAR': 1789.0, 'SUNACTIVITY': 118.1}, {'YEAR': 1790.0, 'SUNACTIVITY': 89.9}, {'YEAR': 1791.0, 'SUNACTIVITY': 66.6}, {'YEAR': 1792.0, 'SUNACTIVITY': 60.0}, {'YEAR': 1793.0, 'SUNACTIVITY': 46.9}, {'YEAR': 1794.0, 'SUNACTIVITY': 41.0}, {'YEAR': 1795.0, 'SUNACTIVITY': 21.3}, {'YEAR': 1796.0, 'SUNACTIVITY': 16.0}, {'YEAR': 1797.0, 'SUNACTIVITY': 6.4}, {'YEAR': 1798.0, 'SUNACTIVITY': 4.1}, {'YEAR': 1799.0, 'SUNACTIVITY': 6.8}, {'YEAR': 1800.0, 'SUNACTIVITY': 14.5}, {'YEAR': 1801.0, 'SUNACTIVITY': 34.0}, {'YEAR': 1802.0, 'SUNACTIVITY': 45.0}, {'YEAR': 1803.0, 'SUNACTIVITY': 43.1}, {'YEAR': 1804.0, 'SUNACTIVITY': 47.5}, {'YEAR': 1805.0, 'SUNACTIVITY': 42.2}, {'YEAR': 1806.0, 'SUNACTIVITY': 28.1}, {'YEAR': 1807.0, 'SUNACTIVITY': 10.1}, {'YEAR': 1808.0, 'SUNACTIVITY': 8.1}, {'YEAR': 1809.0, 'SUNACTIVITY': 2.5}, {'YEAR': 1810.0, 'SUNACTIVITY': 0.0}, {'YEAR': 1811.0, 'SUNACTIVITY': 1.4}, {'YEAR': 1812.0, 'SUNACTIVITY': 5.0}, {'YEAR': 1813.0, 'SUNACTIVITY': 12.2}, {'YEAR': 1814.0, 'SUNACTIVITY': 13.9}, {'YEAR': 1815.0, 'SUNACTIVITY': 35.4}, {'YEAR': 1816.0, 'SUNACTIVITY': 45.8}, {'YEAR': 1817.0, 'SUNACTIVITY': 41.1}, {'YEAR': 1818.0, 'SUNACTIVITY': 30.1}, {'YEAR': 1819.0, 'SUNACTIVITY': 23.9}, {'YEAR': 1820.0, 'SUNACTIVITY': 15.6}, {'YEAR': 1821.0, 'SUNACTIVITY': 6.6}, {'YEAR': 1822.0, 'SUNACTIVITY': 4.0}, {'YEAR': 1823.0, 'SUNACTIVITY': 1.8}, {'YEAR': 1824.0, 'SUNACTIVITY': 8.5}, {'YEAR': 1825.0, 'SUNACTIVITY': 16.6}, {'YEAR': 1826.0, 'SUNACTIVITY': 36.3}, {'YEAR': 1827.0, 'SUNACTIVITY': 49.6}, {'YEAR': 1828.0, 'SUNACTIVITY': 64.2}, {'YEAR': 1829.0, 'SUNACTIVITY': 67.0}, {'YEAR': 1830.0, 'SUNACTIVITY': 70.9}, {'YEAR': 1831.0, 'SUNACTIVITY': 47.8}, {'YEAR': 1832.0, 'SUNACTIVITY': 27.5}, {'YEAR': 1833.0, 'SUNACTIVITY': 8.5}, {'YEAR': 1834.0, 'SUNACTIVITY': 13.2}, {'YEAR': 1835.0, 'SUNACTIVITY': 56.9}, {'YEAR': 1836.0, 'SUNACTIVITY': 121.5}, {'YEAR': 1837.0, 'SUNACTIVITY': 138.3}, {'YEAR': 1838.0, 'SUNACTIVITY': 103.2}, {'YEAR': 1839.0, 'SUNACTIVITY': 85.7}, {'YEAR': 1840.0, 'SUNACTIVITY': 64.6}, {'YEAR': 1841.0, 'SUNACTIVITY': 36.7}, {'YEAR': 1842.0, 'SUNACTIVITY': 24.2}, {'YEAR': 1843.0, 'SUNACTIVITY': 10.7}, {'YEAR': 1844.0, 'SUNACTIVITY': 15.0}, {'YEAR': 1845.0, 'SUNACTIVITY': 40.1}, {'YEAR': 1846.0, 'SUNACTIVITY': 61.5}, {'YEAR': 1847.0, 'SUNACTIVITY': 98.5}, {'YEAR': 1848.0, 'SUNACTIVITY': 124.7}, {'YEAR': 1849.0, 'SUNACTIVITY': 96.3}, {'YEAR': 1850.0, 'SUNACTIVITY': 66.6}, {'YEAR': 1851.0, 'SUNACTIVITY': 64.5}, {'YEAR': 1852.0, 'SUNACTIVITY': 54.1}, {'YEAR': 1853.0, 'SUNACTIVITY': 39.0}, {'YEAR': 1854.0, 'SUNACTIVITY': 20.6}, {'YEAR': 1855.0, 'SUNACTIVITY': 6.7}, {'YEAR': 1856.0, 'SUNACTIVITY': 4.3}, {'YEAR': 1857.0, 'SUNACTIVITY': 22.7}, {'YEAR': 1858.0, 'SUNACTIVITY': 54.8}, {'YEAR': 1859.0, 'SUNACTIVITY': 93.8}, {'YEAR': 1860.0, 'SUNACTIVITY': 95.8}, {'YEAR': 1861.0, 'SUNACTIVITY': 77.2}, {'YEAR': 1862.0, 'SUNACTIVITY': 59.1}, {'YEAR': 1863.0, 'SUNACTIVITY': 44.0}, {'YEAR': 1864.0, 'SUNACTIVITY': 47.0}, {'YEAR': 1865.0, 'SUNACTIVITY': 30.5}, {'YEAR': 1866.0, 'SUNACTIVITY': 16.3}, {'YEAR': 1867.0, 'SUNACTIVITY': 7.3}, {'YEAR': 1868.0, 'SUNACTIVITY': 37.6}, {'YEAR': 1869.0, 'SUNACTIVITY': 74.0}, {'YEAR': 1870.0, 'SUNACTIVITY': 139.0}, {'YEAR': 1871.0, 'SUNACTIVITY': 111.2}, {'YEAR': 1872.0, 'SUNACTIVITY': 101.6}, {'YEAR': 1873.0, 'SUNACTIVITY': 66.2}, {'YEAR': 1874.0, 'SUNACTIVITY': 44.7}, {'YEAR': 1875.0, 'SUNACTIVITY': 17.0}, {'YEAR': 1876.0, 'SUNACTIVITY': 11.3}, {'YEAR': 1877.0, 'SUNACTIVITY': 12.4}, {'YEAR': 1878.0, 'SUNACTIVITY': 3.4}, {'YEAR': 1879.0, 'SUNACTIVITY': 6.0}, {'YEAR': 1880.0, 'SUNACTIVITY': 32.3}, {'YEAR': 1881.0, 'SUNACTIVITY': 54.3}, {'YEAR': 1882.0, 'SUNACTIVITY': 59.7}, {'YEAR': 1883.0, 'SUNACTIVITY': 63.7}, {'YEAR': 1884.0, 'SUNACTIVITY': 63.5}, {'YEAR': 1885.0, 'SUNACTIVITY': 52.2}, {'YEAR': 1886.0, 'SUNACTIVITY': 25.4}, {'YEAR': 1887.0, 'SUNACTIVITY': 13.1}, {'YEAR': 1888.0, 'SUNACTIVITY': 6.8}, {'YEAR': 1889.0, 'SUNACTIVITY': 6.3}, {'YEAR': 1890.0, 'SUNACTIVITY': 7.1}, {'YEAR': 1891.0, 'SUNACTIVITY': 35.6}, {'YEAR': 1892.0, 'SUNACTIVITY': 73.0}, {'YEAR': 1893.0, 'SUNACTIVITY': 85.1}, {'YEAR': 1894.0, 'SUNACTIVITY': 78.0}, {'YEAR': 1895.0, 'SUNACTIVITY': 64.0}, {'YEAR': 1896.0, 'SUNACTIVITY': 41.8}, {'YEAR': 1897.0, 'SUNACTIVITY': 26.2}, {'YEAR': 1898.0, 'SUNACTIVITY': 26.7}, {'YEAR': 1899.0, 'SUNACTIVITY': 12.1}, {'YEAR': 1900.0, 'SUNACTIVITY': 9.5}, {'YEAR': 1901.0, 'SUNACTIVITY': 2.7}, {'YEAR': 1902.0, 'SUNACTIVITY': 5.0}, {'YEAR': 1903.0, 'SUNACTIVITY': 24.4}, {'YEAR': 1904.0, 'SUNACTIVITY': 42.0}, {'YEAR': 1905.0, 'SUNACTIVITY': 63.5}, {'YEAR': 1906.0, 'SUNACTIVITY': 53.8}, {'YEAR': 1907.0, 'SUNACTIVITY': 62.0}, {'YEAR': 1908.0, 'SUNACTIVITY': 48.5}, {'YEAR': 1909.0, 'SUNACTIVITY': 43.9}, {'YEAR': 1910.0, 'SUNACTIVITY': 18.6}, {'YEAR': 1911.0, 'SUNACTIVITY': 5.7}, {'YEAR': 1912.0, 'SUNACTIVITY': 3.6}, {'YEAR': 1913.0, 'SUNACTIVITY': 1.4}, {'YEAR': 1914.0, 'SUNACTIVITY': 9.6}, {'YEAR': 1915.0, 'SUNACTIVITY': 47.4}, {'YEAR': 1916.0, 'SUNACTIVITY': 57.1}, {'YEAR': 1917.0, 'SUNACTIVITY': 103.9}, {'YEAR': 1918.0, 'SUNACTIVITY': 80.6}, {'YEAR': 1919.0, 'SUNACTIVITY': 63.6}, {'YEAR': 1920.0, 'SUNACTIVITY': 37.6}, {'YEAR': 1921.0, 'SUNACTIVITY': 26.1}, {'YEAR': 1922.0, 'SUNACTIVITY': 14.2}, {'YEAR': 1923.0, 'SUNACTIVITY': 5.8}, {'YEAR': 1924.0, 'SUNACTIVITY': 16.7}, {'YEAR': 1925.0, 'SUNACTIVITY': 44.3}, {'YEAR': 1926.0, 'SUNACTIVITY': 63.9}, {'YEAR': 1927.0, 'SUNACTIVITY': 69.0}, {'YEAR': 1928.0, 'SUNACTIVITY': 77.8}, {'YEAR': 1929.0, 'SUNACTIVITY': 64.9}, {'YEAR': 1930.0, 'SUNACTIVITY': 35.7}, {'YEAR': 1931.0, 'SUNACTIVITY': 21.2}, {'YEAR': 1932.0, 'SUNACTIVITY': 11.1}, {'YEAR': 1933.0, 'SUNACTIVITY': 5.7}, {'YEAR': 1934.0, 'SUNACTIVITY': 8.7}, {'YEAR': 1935.0, 'SUNACTIVITY': 36.1}, {'YEAR': 1936.0, 'SUNACTIVITY': 79.7}, {'YEAR': 1937.0, 'SUNACTIVITY': 114.4}, {'YEAR': 1938.0, 'SUNACTIVITY': 109.6}, {'YEAR': 1939.0, 'SUNACTIVITY': 88.8}, {'YEAR': 1940.0, 'SUNACTIVITY': 67.8}, {'YEAR': 1941.0, 'SUNACTIVITY': 47.5}, {'YEAR': 1942.0, 'SUNACTIVITY': 30.6}, {'YEAR': 1943.0, 'SUNACTIVITY': 16.3}, {'YEAR': 1944.0, 'SUNACTIVITY': 9.6}, {'YEAR': 1945.0, 'SUNACTIVITY': 33.2}, {'YEAR': 1946.0, 'SUNACTIVITY': 92.6}, {'YEAR': 1947.0, 'SUNACTIVITY': 151.6}, {'YEAR': 1948.0, 'SUNACTIVITY': 136.3}, {'YEAR': 1949.0, 'SUNACTIVITY': 134.7}, {'YEAR': 1950.0, 'SUNACTIVITY': 83.9}, {'YEAR': 1951.0, 'SUNACTIVITY': 69.4}, {'YEAR': 1952.0, 'SUNACTIVITY': 31.5}, {'YEAR': 1953.0, 'SUNACTIVITY': 13.9}, {'YEAR': 1954.0, 'SUNACTIVITY': 4.4}, {'YEAR': 1955.0, 'SUNACTIVITY': 38.0}, {'YEAR': 1956.0, 'SUNACTIVITY': 141.7}, {'YEAR': 1957.0, 'SUNACTIVITY': 190.2}, {'YEAR': 1958.0, 'SUNACTIVITY': 184.8}, {'YEAR': 1959.0, 'SUNACTIVITY': 159.0}, {'YEAR': 1960.0, 'SUNACTIVITY': 112.3}, {'YEAR': 1961.0, 'SUNACTIVITY': 53.9}, {'YEAR': 1962.0, 'SUNACTIVITY': 37.6}, {'YEAR': 1963.0, 'SUNACTIVITY': 27.9}, {'YEAR': 1964.0, 'SUNACTIVITY': 10.2}, {'YEAR': 1965.0, 'SUNACTIVITY': 15.1}, {'YEAR': 1966.0, 'SUNACTIVITY': 47.0}, {'YEAR': 1967.0, 'SUNACTIVITY': 93.8}, {'YEAR': 1968.0, 'SUNACTIVITY': 105.9}, {'YEAR': 1969.0, 'SUNACTIVITY': 105.5}, {'YEAR': 1970.0, 'SUNACTIVITY': 104.5}, {'YEAR': 1971.0, 'SUNACTIVITY': 66.6}, {'YEAR': 1972.0, 'SUNACTIVITY': 68.9}, {'YEAR': 1973.0, 'SUNACTIVITY': 38.0}, {'YEAR': 1974.0, 'SUNACTIVITY': 34.5}, {'YEAR': 1975.0, 'SUNACTIVITY': 15.5}, {'YEAR': 1976.0, 'SUNACTIVITY': 12.6}, {'YEAR': 1977.0, 'SUNACTIVITY': 27.5}, {'YEAR': 1978.0, 'SUNACTIVITY': 92.5}, {'YEAR': 1979.0, 'SUNACTIVITY': 155.4}, {'YEAR': 1980.0, 'SUNACTIVITY': 154.6}, {'YEAR': 1981.0, 'SUNACTIVITY': 140.4}, {'YEAR': 1982.0, 'SUNACTIVITY': 115.9}, {'YEAR': 1983.0, 'SUNACTIVITY': 66.6}, {'YEAR': 1984.0, 'SUNACTIVITY': 45.9}, {'YEAR': 1985.0, 'SUNACTIVITY': 17.9}, {'YEAR': 1986.0, 'SUNACTIVITY': 13.4}, {'YEAR': 1987.0, 'SUNACTIVITY': 29.4}, {'YEAR': 1988.0, 'SUNACTIVITY': 100.2}, {'YEAR': 1989.0, 'SUNACTIVITY': 157.6}, {'YEAR': 1990.0, 'SUNACTIVITY': 142.6}, {'YEAR': 1991.0, 'SUNACTIVITY': 145.7}, {'YEAR': 1992.0, 'SUNACTIVITY': 94.3}, {'YEAR': 1993.0, 'SUNACTIVITY': 54.6}, {'YEAR': 1994.0, 'SUNACTIVITY': 29.9}, {'YEAR': 1995.0, 'SUNACTIVITY': 17.5}, {'YEAR': 1996.0, 'SUNACTIVITY': 8.6}, {'YEAR': 1997.0, 'SUNACTIVITY': 21.5}, {'YEAR': 1998.0, 'SUNACTIVITY': 64.3}, {'YEAR': 1999.0, 'SUNACTIVITY': 93.3}, {'YEAR': 2000.0, 'SUNACTIVITY': 119.6}, {'YEAR': 2001.0, 'SUNACTIVITY': 111.0}, {'YEAR': 2002.0, 'SUNACTIVITY': 104.0}, {'YEAR': 2003.0, 'SUNACTIVITY': 63.7}, {'YEAR': 2004.0, 'SUNACTIVITY': 40.4}, {'YEAR': 2005.0, 'SUNACTIVITY': 29.8}, {'YEAR': 2006.0, 'SUNACTIVITY': 15.2}, {'YEAR': 2007.0, 'SUNACTIVITY': 7.5}, {'YEAR': 2008.0, 'SUNACTIVITY': 2.9}])\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/victor/anaconda3/lib/python3.6/site-packages/statsmodels/compat/pandas.py:56: FutureWarning: The pandas.core.datetools module is deprecated and will be removed in a future version. Please use the pandas.tseries module instead.\n",
      "  from pandas.core import datetools\n"
     ]
    }
   ],
   "source": [
    "from pymongo import MongoClient\n",
    "import statsmodels.api as sm\n",
    "import json\n",
    "\n",
    "client = MongoClient()\n",
    "db = client.test_database\n",
    "\n",
    "data_loader = sm.datasets.sunspots.load_pandas()\n",
    "df = data_loader.data\n",
    "rows = json.loads(df.T.to_json()).values()\n",
    "print(df)\n",
    "print(rows)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/victor/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: DeprecationWarning: insert is deprecated. Use insert_one or insert_many instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[ObjectId('5adc022d01c1a5492d99709f'),\n",
       " ObjectId('5adc022d01c1a5492d9970a0'),\n",
       " ObjectId('5adc022d01c1a5492d9970a1'),\n",
       " ObjectId('5adc022d01c1a5492d9970a2'),\n",
       " ObjectId('5adc022d01c1a5492d9970a3'),\n",
       " ObjectId('5adc022d01c1a5492d9970a4'),\n",
       " ObjectId('5adc022d01c1a5492d9970a5'),\n",
       " ObjectId('5adc022d01c1a5492d9970a6'),\n",
       " ObjectId('5adc022d01c1a5492d9970a7'),\n",
       " ObjectId('5adc022d01c1a5492d9970a8'),\n",
       " ObjectId('5adc022d01c1a5492d9970a9'),\n",
       " ObjectId('5adc022d01c1a5492d9970aa'),\n",
       " ObjectId('5adc022d01c1a5492d9970ab'),\n",
       " ObjectId('5adc022d01c1a5492d9970ac'),\n",
       " ObjectId('5adc022d01c1a5492d9970ad'),\n",
       " ObjectId('5adc022d01c1a5492d9970ae'),\n",
       " ObjectId('5adc022d01c1a5492d9970af'),\n",
       " ObjectId('5adc022d01c1a5492d9970b0'),\n",
       " ObjectId('5adc022d01c1a5492d9970b1'),\n",
       " ObjectId('5adc022d01c1a5492d9970b2'),\n",
       " ObjectId('5adc022d01c1a5492d9970b3'),\n",
       " ObjectId('5adc022d01c1a5492d9970b4'),\n",
       " ObjectId('5adc022d01c1a5492d9970b5'),\n",
       " ObjectId('5adc022d01c1a5492d9970b6'),\n",
       " ObjectId('5adc022d01c1a5492d9970b7'),\n",
       " ObjectId('5adc022d01c1a5492d9970b8'),\n",
       " ObjectId('5adc022d01c1a5492d9970b9'),\n",
       " ObjectId('5adc022d01c1a5492d9970ba'),\n",
       " ObjectId('5adc022d01c1a5492d9970bb'),\n",
       " ObjectId('5adc022d01c1a5492d9970bc'),\n",
       " ObjectId('5adc022d01c1a5492d9970bd'),\n",
       " ObjectId('5adc022d01c1a5492d9970be'),\n",
       " ObjectId('5adc022d01c1a5492d9970bf'),\n",
       " ObjectId('5adc022d01c1a5492d9970c0'),\n",
       " ObjectId('5adc022d01c1a5492d9970c1'),\n",
       " ObjectId('5adc022d01c1a5492d9970c2'),\n",
       " ObjectId('5adc022d01c1a5492d9970c3'),\n",
       " ObjectId('5adc022d01c1a5492d9970c4'),\n",
       " ObjectId('5adc022d01c1a5492d9970c5'),\n",
       " ObjectId('5adc022d01c1a5492d9970c6'),\n",
       " ObjectId('5adc022d01c1a5492d9970c7'),\n",
       " ObjectId('5adc022d01c1a5492d9970c8'),\n",
       " ObjectId('5adc022d01c1a5492d9970c9'),\n",
       " ObjectId('5adc022d01c1a5492d9970ca'),\n",
       " ObjectId('5adc022d01c1a5492d9970cb'),\n",
       " ObjectId('5adc022d01c1a5492d9970cc'),\n",
       " ObjectId('5adc022d01c1a5492d9970cd'),\n",
       " ObjectId('5adc022d01c1a5492d9970ce'),\n",
       " ObjectId('5adc022d01c1a5492d9970cf'),\n",
       " ObjectId('5adc022d01c1a5492d9970d0'),\n",
       " ObjectId('5adc022d01c1a5492d9970d1'),\n",
       " ObjectId('5adc022d01c1a5492d9970d2'),\n",
       " ObjectId('5adc022d01c1a5492d9970d3'),\n",
       " ObjectId('5adc022d01c1a5492d9970d4'),\n",
       " ObjectId('5adc022d01c1a5492d9970d5'),\n",
       " ObjectId('5adc022d01c1a5492d9970d6'),\n",
       " ObjectId('5adc022d01c1a5492d9970d7'),\n",
       " ObjectId('5adc022d01c1a5492d9970d8'),\n",
       " ObjectId('5adc022d01c1a5492d9970d9'),\n",
       " ObjectId('5adc022d01c1a5492d9970da'),\n",
       " ObjectId('5adc022d01c1a5492d9970db'),\n",
       " ObjectId('5adc022d01c1a5492d9970dc'),\n",
       " ObjectId('5adc022d01c1a5492d9970dd'),\n",
       " ObjectId('5adc022d01c1a5492d9970de'),\n",
       " ObjectId('5adc022d01c1a5492d9970df'),\n",
       " ObjectId('5adc022d01c1a5492d9970e0'),\n",
       " ObjectId('5adc022d01c1a5492d9970e1'),\n",
       " ObjectId('5adc022d01c1a5492d9970e2'),\n",
       " ObjectId('5adc022d01c1a5492d9970e3'),\n",
       " ObjectId('5adc022d01c1a5492d9970e4'),\n",
       " ObjectId('5adc022d01c1a5492d9970e5'),\n",
       " ObjectId('5adc022d01c1a5492d9970e6'),\n",
       " ObjectId('5adc022d01c1a5492d9970e7'),\n",
       " ObjectId('5adc022d01c1a5492d9970e8'),\n",
       " ObjectId('5adc022d01c1a5492d9970e9'),\n",
       " ObjectId('5adc022d01c1a5492d9970ea'),\n",
       " ObjectId('5adc022d01c1a5492d9970eb'),\n",
       " ObjectId('5adc022d01c1a5492d9970ec'),\n",
       " ObjectId('5adc022d01c1a5492d9970ed'),\n",
       " ObjectId('5adc022d01c1a5492d9970ee'),\n",
       " ObjectId('5adc022d01c1a5492d9970ef'),\n",
       " ObjectId('5adc022d01c1a5492d9970f0'),\n",
       " ObjectId('5adc022d01c1a5492d9970f1'),\n",
       " ObjectId('5adc022d01c1a5492d9970f2'),\n",
       " ObjectId('5adc022d01c1a5492d9970f3'),\n",
       " ObjectId('5adc022d01c1a5492d9970f4'),\n",
       " ObjectId('5adc022d01c1a5492d9970f5'),\n",
       " ObjectId('5adc022d01c1a5492d9970f6'),\n",
       " ObjectId('5adc022d01c1a5492d9970f7'),\n",
       " ObjectId('5adc022d01c1a5492d9970f8'),\n",
       " ObjectId('5adc022d01c1a5492d9970f9'),\n",
       " ObjectId('5adc022d01c1a5492d9970fa'),\n",
       " ObjectId('5adc022d01c1a5492d9970fb'),\n",
       " ObjectId('5adc022d01c1a5492d9970fc'),\n",
       " ObjectId('5adc022d01c1a5492d9970fd'),\n",
       " ObjectId('5adc022d01c1a5492d9970fe'),\n",
       " ObjectId('5adc022d01c1a5492d9970ff'),\n",
       " ObjectId('5adc022d01c1a5492d997100'),\n",
       " ObjectId('5adc022d01c1a5492d997101'),\n",
       " ObjectId('5adc022d01c1a5492d997102'),\n",
       " ObjectId('5adc022d01c1a5492d997103'),\n",
       " ObjectId('5adc022d01c1a5492d997104'),\n",
       " ObjectId('5adc022d01c1a5492d997105'),\n",
       " ObjectId('5adc022d01c1a5492d997106'),\n",
       " ObjectId('5adc022d01c1a5492d997107'),\n",
       " ObjectId('5adc022d01c1a5492d997108'),\n",
       " ObjectId('5adc022d01c1a5492d997109'),\n",
       " ObjectId('5adc022d01c1a5492d99710a'),\n",
       " ObjectId('5adc022d01c1a5492d99710b'),\n",
       " ObjectId('5adc022d01c1a5492d99710c'),\n",
       " ObjectId('5adc022d01c1a5492d99710d'),\n",
       " ObjectId('5adc022d01c1a5492d99710e'),\n",
       " ObjectId('5adc022d01c1a5492d99710f'),\n",
       " ObjectId('5adc022d01c1a5492d997110'),\n",
       " ObjectId('5adc022d01c1a5492d997111'),\n",
       " ObjectId('5adc022d01c1a5492d997112'),\n",
       " ObjectId('5adc022d01c1a5492d997113'),\n",
       " ObjectId('5adc022d01c1a5492d997114'),\n",
       " ObjectId('5adc022d01c1a5492d997115'),\n",
       " ObjectId('5adc022d01c1a5492d997116'),\n",
       " ObjectId('5adc022d01c1a5492d997117'),\n",
       " ObjectId('5adc022d01c1a5492d997118'),\n",
       " ObjectId('5adc022d01c1a5492d997119'),\n",
       " ObjectId('5adc022d01c1a5492d99711a'),\n",
       " ObjectId('5adc022d01c1a5492d99711b'),\n",
       " ObjectId('5adc022d01c1a5492d99711c'),\n",
       " ObjectId('5adc022d01c1a5492d99711d'),\n",
       " ObjectId('5adc022d01c1a5492d99711e'),\n",
       " ObjectId('5adc022d01c1a5492d99711f'),\n",
       " ObjectId('5adc022d01c1a5492d997120'),\n",
       " ObjectId('5adc022d01c1a5492d997121'),\n",
       " ObjectId('5adc022d01c1a5492d997122'),\n",
       " ObjectId('5adc022d01c1a5492d997123'),\n",
       " ObjectId('5adc022d01c1a5492d997124'),\n",
       " ObjectId('5adc022d01c1a5492d997125'),\n",
       " ObjectId('5adc022d01c1a5492d997126'),\n",
       " ObjectId('5adc022d01c1a5492d997127'),\n",
       " ObjectId('5adc022d01c1a5492d997128'),\n",
       " ObjectId('5adc022d01c1a5492d997129'),\n",
       " ObjectId('5adc022d01c1a5492d99712a'),\n",
       " ObjectId('5adc022d01c1a5492d99712b'),\n",
       " ObjectId('5adc022d01c1a5492d99712c'),\n",
       " ObjectId('5adc022d01c1a5492d99712d'),\n",
       " ObjectId('5adc022d01c1a5492d99712e'),\n",
       " ObjectId('5adc022d01c1a5492d99712f'),\n",
       " ObjectId('5adc022d01c1a5492d997130'),\n",
       " ObjectId('5adc022d01c1a5492d997131'),\n",
       " ObjectId('5adc022d01c1a5492d997132'),\n",
       " ObjectId('5adc022d01c1a5492d997133'),\n",
       " ObjectId('5adc022d01c1a5492d997134'),\n",
       " ObjectId('5adc022d01c1a5492d997135'),\n",
       " ObjectId('5adc022d01c1a5492d997136'),\n",
       " ObjectId('5adc022d01c1a5492d997137'),\n",
       " ObjectId('5adc022d01c1a5492d997138'),\n",
       " ObjectId('5adc022d01c1a5492d997139'),\n",
       " ObjectId('5adc022d01c1a5492d99713a'),\n",
       " ObjectId('5adc022d01c1a5492d99713b'),\n",
       " ObjectId('5adc022d01c1a5492d99713c'),\n",
       " ObjectId('5adc022d01c1a5492d99713d'),\n",
       " ObjectId('5adc022d01c1a5492d99713e'),\n",
       " ObjectId('5adc022d01c1a5492d99713f'),\n",
       " ObjectId('5adc022d01c1a5492d997140'),\n",
       " ObjectId('5adc022d01c1a5492d997141'),\n",
       " ObjectId('5adc022d01c1a5492d997142'),\n",
       " ObjectId('5adc022d01c1a5492d997143'),\n",
       " ObjectId('5adc022d01c1a5492d997144'),\n",
       " ObjectId('5adc022d01c1a5492d997145'),\n",
       " ObjectId('5adc022d01c1a5492d997146'),\n",
       " ObjectId('5adc022d01c1a5492d997147'),\n",
       " ObjectId('5adc022d01c1a5492d997148'),\n",
       " ObjectId('5adc022d01c1a5492d997149'),\n",
       " ObjectId('5adc022d01c1a5492d99714a'),\n",
       " ObjectId('5adc022d01c1a5492d99714b'),\n",
       " ObjectId('5adc022d01c1a5492d99714c'),\n",
       " ObjectId('5adc022d01c1a5492d99714d'),\n",
       " ObjectId('5adc022d01c1a5492d99714e'),\n",
       " ObjectId('5adc022d01c1a5492d99714f'),\n",
       " ObjectId('5adc022d01c1a5492d997150'),\n",
       " ObjectId('5adc022d01c1a5492d997151'),\n",
       " ObjectId('5adc022d01c1a5492d997152'),\n",
       " ObjectId('5adc022d01c1a5492d997153'),\n",
       " ObjectId('5adc022d01c1a5492d997154'),\n",
       " ObjectId('5adc022d01c1a5492d997155'),\n",
       " ObjectId('5adc022d01c1a5492d997156'),\n",
       " ObjectId('5adc022d01c1a5492d997157'),\n",
       " ObjectId('5adc022d01c1a5492d997158'),\n",
       " ObjectId('5adc022d01c1a5492d997159'),\n",
       " ObjectId('5adc022d01c1a5492d99715a'),\n",
       " ObjectId('5adc022d01c1a5492d99715b'),\n",
       " ObjectId('5adc022d01c1a5492d99715c'),\n",
       " ObjectId('5adc022d01c1a5492d99715d'),\n",
       " ObjectId('5adc022d01c1a5492d99715e'),\n",
       " ObjectId('5adc022d01c1a5492d99715f'),\n",
       " ObjectId('5adc022d01c1a5492d997160'),\n",
       " ObjectId('5adc022d01c1a5492d997161'),\n",
       " ObjectId('5adc022d01c1a5492d997162'),\n",
       " ObjectId('5adc022d01c1a5492d997163'),\n",
       " ObjectId('5adc022d01c1a5492d997164'),\n",
       " ObjectId('5adc022d01c1a5492d997165'),\n",
       " ObjectId('5adc022d01c1a5492d997166'),\n",
       " ObjectId('5adc022d01c1a5492d997167'),\n",
       " ObjectId('5adc022d01c1a5492d997168'),\n",
       " ObjectId('5adc022d01c1a5492d997169'),\n",
       " ObjectId('5adc022d01c1a5492d99716a'),\n",
       " ObjectId('5adc022d01c1a5492d99716b'),\n",
       " ObjectId('5adc022d01c1a5492d99716c'),\n",
       " ObjectId('5adc022d01c1a5492d99716d'),\n",
       " ObjectId('5adc022d01c1a5492d99716e'),\n",
       " ObjectId('5adc022d01c1a5492d99716f'),\n",
       " ObjectId('5adc022d01c1a5492d997170'),\n",
       " ObjectId('5adc022d01c1a5492d997171'),\n",
       " ObjectId('5adc022d01c1a5492d997172'),\n",
       " ObjectId('5adc022d01c1a5492d997173'),\n",
       " ObjectId('5adc022d01c1a5492d997174'),\n",
       " ObjectId('5adc022d01c1a5492d997175'),\n",
       " ObjectId('5adc022d01c1a5492d997176'),\n",
       " ObjectId('5adc022d01c1a5492d997177'),\n",
       " ObjectId('5adc022d01c1a5492d997178'),\n",
       " ObjectId('5adc022d01c1a5492d997179'),\n",
       " ObjectId('5adc022d01c1a5492d99717a'),\n",
       " ObjectId('5adc022d01c1a5492d99717b'),\n",
       " ObjectId('5adc022d01c1a5492d99717c'),\n",
       " ObjectId('5adc022d01c1a5492d99717d'),\n",
       " ObjectId('5adc022d01c1a5492d99717e'),\n",
       " ObjectId('5adc022d01c1a5492d99717f'),\n",
       " ObjectId('5adc022d01c1a5492d997180'),\n",
       " ObjectId('5adc022d01c1a5492d997181'),\n",
       " ObjectId('5adc022d01c1a5492d997182'),\n",
       " ObjectId('5adc022d01c1a5492d997183'),\n",
       " ObjectId('5adc022d01c1a5492d997184'),\n",
       " ObjectId('5adc022d01c1a5492d997185'),\n",
       " ObjectId('5adc022d01c1a5492d997186'),\n",
       " ObjectId('5adc022d01c1a5492d997187'),\n",
       " ObjectId('5adc022d01c1a5492d997188'),\n",
       " ObjectId('5adc022d01c1a5492d997189'),\n",
       " ObjectId('5adc022d01c1a5492d99718a'),\n",
       " ObjectId('5adc022d01c1a5492d99718b'),\n",
       " ObjectId('5adc022d01c1a5492d99718c'),\n",
       " ObjectId('5adc022d01c1a5492d99718d'),\n",
       " ObjectId('5adc022d01c1a5492d99718e'),\n",
       " ObjectId('5adc022d01c1a5492d99718f'),\n",
       " ObjectId('5adc022d01c1a5492d997190'),\n",
       " ObjectId('5adc022d01c1a5492d997191'),\n",
       " ObjectId('5adc022d01c1a5492d997192'),\n",
       " ObjectId('5adc022d01c1a5492d997193'),\n",
       " ObjectId('5adc022d01c1a5492d997194'),\n",
       " ObjectId('5adc022d01c1a5492d997195'),\n",
       " ObjectId('5adc022d01c1a5492d997196'),\n",
       " ObjectId('5adc022d01c1a5492d997197'),\n",
       " ObjectId('5adc022d01c1a5492d997198'),\n",
       " ObjectId('5adc022d01c1a5492d997199'),\n",
       " ObjectId('5adc022d01c1a5492d99719a'),\n",
       " ObjectId('5adc022d01c1a5492d99719b'),\n",
       " ObjectId('5adc022d01c1a5492d99719c'),\n",
       " ObjectId('5adc022d01c1a5492d99719d'),\n",
       " ObjectId('5adc022d01c1a5492d99719e'),\n",
       " ObjectId('5adc022d01c1a5492d99719f'),\n",
       " ObjectId('5adc022d01c1a5492d9971a0'),\n",
       " ObjectId('5adc022d01c1a5492d9971a1'),\n",
       " ObjectId('5adc022d01c1a5492d9971a2'),\n",
       " ObjectId('5adc022d01c1a5492d9971a3'),\n",
       " ObjectId('5adc022d01c1a5492d9971a4'),\n",
       " ObjectId('5adc022d01c1a5492d9971a5'),\n",
       " ObjectId('5adc022d01c1a5492d9971a6'),\n",
       " ObjectId('5adc022d01c1a5492d9971a7'),\n",
       " ObjectId('5adc022d01c1a5492d9971a8'),\n",
       " ObjectId('5adc022d01c1a5492d9971a9'),\n",
       " ObjectId('5adc022d01c1a5492d9971aa'),\n",
       " ObjectId('5adc022d01c1a5492d9971ab'),\n",
       " ObjectId('5adc022d01c1a5492d9971ac'),\n",
       " ObjectId('5adc022d01c1a5492d9971ad'),\n",
       " ObjectId('5adc022d01c1a5492d9971ae'),\n",
       " ObjectId('5adc022d01c1a5492d9971af'),\n",
       " ObjectId('5adc022d01c1a5492d9971b0'),\n",
       " ObjectId('5adc022d01c1a5492d9971b1'),\n",
       " ObjectId('5adc022d01c1a5492d9971b2'),\n",
       " ObjectId('5adc022d01c1a5492d9971b3'),\n",
       " ObjectId('5adc022d01c1a5492d9971b4'),\n",
       " ObjectId('5adc022d01c1a5492d9971b5'),\n",
       " ObjectId('5adc022d01c1a5492d9971b6'),\n",
       " ObjectId('5adc022d01c1a5492d9971b7'),\n",
       " ObjectId('5adc022d01c1a5492d9971b8'),\n",
       " ObjectId('5adc022d01c1a5492d9971b9'),\n",
       " ObjectId('5adc022d01c1a5492d9971ba'),\n",
       " ObjectId('5adc022d01c1a5492d9971bb'),\n",
       " ObjectId('5adc022d01c1a5492d9971bc'),\n",
       " ObjectId('5adc022d01c1a5492d9971bd'),\n",
       " ObjectId('5adc022d01c1a5492d9971be'),\n",
       " ObjectId('5adc022d01c1a5492d9971bf'),\n",
       " ObjectId('5adc022d01c1a5492d9971c0'),\n",
       " ObjectId('5adc022d01c1a5492d9971c1'),\n",
       " ObjectId('5adc022d01c1a5492d9971c2'),\n",
       " ObjectId('5adc022d01c1a5492d9971c3'),\n",
       " ObjectId('5adc022d01c1a5492d9971c4'),\n",
       " ObjectId('5adc022d01c1a5492d9971c5'),\n",
       " ObjectId('5adc022d01c1a5492d9971c6'),\n",
       " ObjectId('5adc022d01c1a5492d9971c7'),\n",
       " ObjectId('5adc022d01c1a5492d9971c8'),\n",
       " ObjectId('5adc022d01c1a5492d9971c9'),\n",
       " ObjectId('5adc022d01c1a5492d9971ca'),\n",
       " ObjectId('5adc022d01c1a5492d9971cb'),\n",
       " ObjectId('5adc022d01c1a5492d9971cc'),\n",
       " ObjectId('5adc022d01c1a5492d9971cd'),\n",
       " ObjectId('5adc022d01c1a5492d9971ce'),\n",
       " ObjectId('5adc022d01c1a5492d9971cf'),\n",
       " ObjectId('5adc022d01c1a5492d9971d0'),\n",
       " ObjectId('5adc022d01c1a5492d9971d1'),\n",
       " ObjectId('5adc022d01c1a5492d9971d2'),\n",
       " ObjectId('5adc022d01c1a5492d9971d3')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sunspots.insert(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     SUNACTIVITY    YEAR                       _id\n",
      "0            5.0  1700.0  5adc01ea01c1a54904a81b5b\n",
      "1           11.0  1701.0  5adc01ea01c1a54904a81b5c\n",
      "2           16.0  1702.0  5adc01ea01c1a54904a81b5d\n",
      "3           23.0  1703.0  5adc01ea01c1a54904a81b5e\n",
      "4           36.0  1704.0  5adc01ea01c1a54904a81b5f\n",
      "5           58.0  1705.0  5adc01ea01c1a54904a81b60\n",
      "6           29.0  1706.0  5adc01ea01c1a54904a81b61\n",
      "7           20.0  1707.0  5adc01ea01c1a54904a81b62\n",
      "8           10.0  1708.0  5adc01ea01c1a54904a81b63\n",
      "9            8.0  1709.0  5adc01ea01c1a54904a81b64\n",
      "10           3.0  1710.0  5adc01ea01c1a54904a81b65\n",
      "11           0.0  1711.0  5adc01ea01c1a54904a81b66\n",
      "12           0.0  1712.0  5adc01ea01c1a54904a81b67\n",
      "13           2.0  1713.0  5adc01ea01c1a54904a81b68\n",
      "14          11.0  1714.0  5adc01ea01c1a54904a81b69\n",
      "15          27.0  1715.0  5adc01ea01c1a54904a81b6a\n",
      "16          47.0  1716.0  5adc01ea01c1a54904a81b6b\n",
      "17          63.0  1717.0  5adc01ea01c1a54904a81b6c\n",
      "18          60.0  1718.0  5adc01ea01c1a54904a81b6d\n",
      "19          39.0  1719.0  5adc01ea01c1a54904a81b6e\n",
      "20          28.0  1720.0  5adc01ea01c1a54904a81b6f\n",
      "21          26.0  1721.0  5adc01ea01c1a54904a81b70\n",
      "22          22.0  1722.0  5adc01ea01c1a54904a81b71\n",
      "23          11.0  1723.0  5adc01ea01c1a54904a81b72\n",
      "24          21.0  1724.0  5adc01ea01c1a54904a81b73\n",
      "25          40.0  1725.0  5adc01ea01c1a54904a81b74\n",
      "26          78.0  1726.0  5adc01ea01c1a54904a81b75\n",
      "27         122.0  1727.0  5adc01ea01c1a54904a81b76\n",
      "28         103.0  1728.0  5adc01ea01c1a54904a81b77\n",
      "29          73.0  1729.0  5adc01ea01c1a54904a81b78\n",
      "..           ...     ...                       ...\n",
      "588        155.4  1979.0  5adc022d01c1a5492d9971b6\n",
      "589        154.6  1980.0  5adc022d01c1a5492d9971b7\n",
      "590        140.4  1981.0  5adc022d01c1a5492d9971b8\n",
      "591        115.9  1982.0  5adc022d01c1a5492d9971b9\n",
      "592         66.6  1983.0  5adc022d01c1a5492d9971ba\n",
      "593         45.9  1984.0  5adc022d01c1a5492d9971bb\n",
      "594         17.9  1985.0  5adc022d01c1a5492d9971bc\n",
      "595         13.4  1986.0  5adc022d01c1a5492d9971bd\n",
      "596         29.4  1987.0  5adc022d01c1a5492d9971be\n",
      "597        100.2  1988.0  5adc022d01c1a5492d9971bf\n",
      "598        157.6  1989.0  5adc022d01c1a5492d9971c0\n",
      "599        142.6  1990.0  5adc022d01c1a5492d9971c1\n",
      "600        145.7  1991.0  5adc022d01c1a5492d9971c2\n",
      "601         94.3  1992.0  5adc022d01c1a5492d9971c3\n",
      "602         54.6  1993.0  5adc022d01c1a5492d9971c4\n",
      "603         29.9  1994.0  5adc022d01c1a5492d9971c5\n",
      "604         17.5  1995.0  5adc022d01c1a5492d9971c6\n",
      "605          8.6  1996.0  5adc022d01c1a5492d9971c7\n",
      "606         21.5  1997.0  5adc022d01c1a5492d9971c8\n",
      "607         64.3  1998.0  5adc022d01c1a5492d9971c9\n",
      "608         93.3  1999.0  5adc022d01c1a5492d9971ca\n",
      "609        119.6  2000.0  5adc022d01c1a5492d9971cb\n",
      "610        111.0  2001.0  5adc022d01c1a5492d9971cc\n",
      "611        104.0  2002.0  5adc022d01c1a5492d9971cd\n",
      "612         63.7  2003.0  5adc022d01c1a5492d9971ce\n",
      "613         40.4  2004.0  5adc022d01c1a5492d9971cf\n",
      "614         29.8  2005.0  5adc022d01c1a5492d9971d0\n",
      "615         15.2  2006.0  5adc022d01c1a5492d9971d1\n",
      "616          7.5  2007.0  5adc022d01c1a5492d9971d2\n",
      "617          2.9  2008.0  5adc022d01c1a5492d9971d3\n",
      "\n",
      "[618 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cursor = db['sunspots'].find({})\n",
    "df = pd.DataFrame(list(cursor))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'nIndexesWas': 1, 'ns': 'test_database.sunspots', 'ok': 1.0}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.drop_collection('sunspots')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Storing data in Redis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              0     1     10      100   101   102     103     104     105  \\\n",
      "SUNACTIVITY     5    11     3    14.5    34    45    43.1    47.5    42.2   \n",
      "YEAR         1700  1701  1710  1800.0  1801  1802  1803.0  1804.0  1805.0   \n",
      "\n",
      "                106   ...       90      91    92      93    94      95    96   \\\n",
      "SUNACTIVITY    28.1   ...      89.9    66.6    60    46.9    41    21.3    16   \n",
      "YEAR         1806.0   ...    1790.0  1791.0  1792  1793.0  1794  1795.0  1796   \n",
      "\n",
      "                97      98      99   \n",
      "SUNACTIVITY     6.4     4.1     6.8  \n",
      "YEAR         1797.0  1798.0  1799.0  \n",
      "\n",
      "[2 rows x 309 columns]\n"
     ]
    }
   ],
   "source": [
    "import redis\n",
    "import statsmodels.api as sm\n",
    "import pandas as pd\n",
    "\n",
    "r = redis.StrictRedis()\n",
    "data_loader = sm.datasets.sunspots.load_pandas()\n",
    "df = data_loader.data\n",
    "data = df.T.to_json()\n",
    "r.set('sunspots', data)\n",
    "blob = r.get('sunspots')\n",
    "print(pd.read_json(blob))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
